import datetime
from view.baseview import BaseView
from orm.model import TransForm

class PersonalSalesView(BaseView):
    '''
        个人月销售情况统计
        参数：BsseView：  对返回参数的操作类
    '''

    def sqlUser(self,timee):
        '''
        获取后台数据库数据
        参数: timee: 查询时间
        :return: 查询的数据列表
        '''

        # 获取本月最后一天
        next_month = datetime.datetime.strptime(timee, "%Y-%m").replace(
            day=28) + datetime.timedelta(days=4)  # this will never fail
        month_max = next_month - datetime.timedelta(days=next_month.day)
        # print(timee + '-01',month_max.strftime('%Y-%m-%d'))

        sql = """
            SELECT
                u.id,
                t.status,
                d.name,
                u.username,
                sum(ifnull( tr.totalprice, 0 )) AS salesVolume,
                tr.collectiondate,
                GROUP_CONCAT(t.id) AS transform_id
            FROM
                transform t
                INNER JOIN transformrecorder tr ON t.id = tr.transform_id
                INNER JOIN USER u ON tr.reciver_id = u.id
                INNER JOIN dept d ON u.dept_id = d.id 
            WHERE
                tr.collectiondate >= '{0}'
                AND tr.collectiondate <= '{1}'
            GROUP BY
                u.username,
                tr.collectiondate 
            HAVING
                t.STATUS = '已审核' 
                OR t.STATUS = '已分配' 
                OR t.STATUS = '已流转'
        """.format(timee + '-01',month_max.strftime('%Y-%m-%d'))

        # 执行sql语句
        rows = TransForm.raw(sql)
        print(sql)
        lists = []
        for row in rows:
            transform_id = []
            for num in row.transform_id.split(','):
                # print(type(int(num)),int(num))
                transform_id.append(int(num))
            # print(transform_id)
            lists.append({
                "id":row.id,
                "status":row.status,
                "name":row.name,
                "username":row.username,
                "salesVolume":float(row.salesVolume),
                "collectiondate":str(row.collectiondate),
                "transform_id":transform_id})

        return lists

    def getdatat(self,timee):
        '''
           个人每月销售数据模板
           参数：timee 查询月份
           返回：数据模板
            JSON：[datat：个人每月销售数据模板，theader：当月日期与星期对应详细情况]
        '''
        # 获取本月最后一天
        next_month = datetime.datetime.strptime(timee, "%Y-%m").replace(
            day=28) + datetime.timedelta(days=4)
        month_max = next_month - datetime.timedelta(days=next_month.day)

        datat = {"name": "", "salesVolume": 0.0}
        theader = []

        for i in range(month_max.day):
            someday = datetime.datetime.strptime(timee + "-" + str(i + 1), "%Y-%m-%d")
            month_day = str(someday.month) + "月" + str(someday.day) + "日"

            datat[month_day] = 0.0
            datat[month_day + '_transformid'] = None

            # 计算当月第几周
            one_time = someday.replace(day=1, hour=0, minute=0, second=0, microsecond=0)  # 当月第一天
            week_num = int(someday.strftime('%W')) - int(one_time.strftime('%W')) + 1  # 当月第几周
            result = {1: "第一周", 2: "第二周", 3: "第三周", 4: "第四周", 5: "第五周", 6: "第六周"}
            week_num_result = result.get(week_num);
            datat[week_num_result] = 0.0  # '第一周': 0.0,

            # 计算周期
            result = {1: "星期一", 2: "星期二", 3: "星期三", 4: "星期四", 5: "星期五", 6: "星期六", 7: "星期日"}
            week = result.get(someday.isoweekday())
            # 生成theader
            is_bool = True
            for dictt in theader:
                if dictt['name'] == week_num_result:  # 判断有无这一周
                    dictt['data'].append({"name": month_day, "data": week})
                    is_bool = False
            if is_bool:
                theader.append({"name": week_num_result, "data": [{"name": month_day, "data": week}]})

        return [datat, theader]

    def getSpanArr(self,datat):
        '''
           计算合并行
           参数：datat： 当月销售情况
           返回：合并行的数据
            JSON：[datat：当月销售情况，spanArr： 合并行的数据列表]
        '''
        data = [];
        dataa = datat['总计'][0]  # 将月总计取出来放到列表最后
        del datat['总计']
        for i in datat.keys():
            # // 每个部门
            dept_data = datat[i];
            z_data = {}
            for n_data in dept_data:
                # // 每个人
                n_data["dept"] = i;
                if n_data['name'] == "部门总计":
                    z_data = n_data  # 将每个部门总计取出来放到部门最后
                else:
                    data.append(n_data);

            data.append(z_data)
        dataa["dept"] = "总计";
        data.append(dataa);

        # pos : 当前对象临时变量
        pos = 0;
        spanArr = [];
        for i in range(len(data)):
            if i == 0:
                spanArr.append(1);
                pos = 0;
            else:
                # 判断当前元素与上一个元素是否相同
                if data[i]['dept'] == data[i - 1]['dept']:  # //相等
                    spanArr[pos] += 1;
                    spanArr.append(0);
                else:  # //不相等
                    spanArr.append(1);
                    pos = i;
        return [data, spanArr]

    def query(self,queryForm):
        '''
           个人销售情况的主查询方法
           参数：queryForm： 前端传的查询数据
           返回：个人销售情况
            JSON：result
        '''
        timee = queryForm['datetime']
        print(timee)

        # 获取后台数据
        lists = self.sqlUser(timee);

        # 添加所有部门的总计
        # 对获取后台数据进行操作
        datat = self.getdatat(timee)[0]
        datat["name"] = "总计"
        data = {"总计": [datat]}
        for row in lists:  # 循环每个人每天的销售情况
            datee = row["collectiondate"]
            someday = datetime.datetime.strptime(datee, '%Y-%m-%d')

            # 计算当月第几周
            one_time = someday.replace(day=1, hour=0, minute=0, second=0, microsecond=0)  # 当月第一天
            week_num = int(someday.strftime('%W')) - int(one_time.strftime('%W')) + 1  # 当月第几周
            result = {1: "第一周", 2: "第二周", 3: "第三周", 4: "第四周", 5: "第五周", 6: "第六周"}
            week_num_result = result.get(week_num);

            # 个人每月销售数据模板
            month_day = str(someday.month) + "月" + str(someday.day) + "日"
            datat = self.getdatat(timee)[0]
            datat["name"] = row['username']
            datat[month_day + '_transformid'] = row['transform_id']
            datat[month_day] = row['salesVolume']
            datat[week_num_result] = row['salesVolume']
            datat["salesVolume"] = row['salesVolume']

            # 统计行总计
            # 统计每天所有人的销售额总量
            data['总计'][0][month_day] += row['salesVolume']
            # 统计每周所有人的销售额总量
            data['总计'][0][week_num_result] += row['salesVolume']
            # 统计每月所有人的销售额总量
            data['总计'][0]["salesVolume"] += row['salesVolume']

            # 查看是否有此部门
            if data.get(row["name"]) == None:
                # 没有此部门 # 创建这个部门并将这个人的信息加进去
                data[row["name"]] = [datat]

                # 添加每个部门的总计
                data_t = self.getdatat(timee)[0]
                data_t["name"] = "部门总计"
                data_t[month_day] = row['salesVolume']
                data_t[week_num_result] = row['salesVolume']
                data_t["salesVolume"] = row['salesVolume']
                data[row["name"]].append(data_t)
            else:  # 有此部门
                is_bool = True
                for data_row in data[row["name"]]:

                    # 统计每个部门总计
                    if data_row["name"] == "部门总计":
                        # 统计每个部门每天所有人的销售额总量
                        data_row[month_day] += row['salesVolume']  # 日总计
                        # 统计每个部门每周所有人的销售额总量
                        data_row[week_num_result] += row['salesVolume']  # 周总计
                        # 统计每个部门每月所有人的销售额总量
                        data_row["salesVolume"] += row['salesVolume']  # 月总计

                    if data_row["name"] != row["username"]: continue  # 查看是否有这个人

                    # 有这个人  统计每个人销售额
                    # 统计每天个人的销售额总量
                    data_row[month_day] += row['salesVolume']
                    data_row[month_day + '_transformid'] = row['transform_id']
                    # 统计每周个人的销售额总量
                    data_row[week_num_result] += row['salesVolume']
                    # 统计每月个人的销售额总量
                    data_row["salesVolume"] += row['salesVolume']
                    is_bool = False

                # 没有这个人
                if is_bool: data[row["name"]].append(datat)

        # 获取表头信息
        theader = self.getdatat(timee)[1]

        # 计算返回具体数据
        data_spanArr = self.getSpanArr(data)

        result = {"data": data_spanArr[0], "theader": theader, "spanArr": data_spanArr[1],"time" : timee}
        return super().resultSet(200, "月销售统计成功", result)

    def getTransFormDatas(self,row_id):
        '''
           流转单详细查询方法
           参数：row_id： 前端传的查询id
           返回：流转单详细列表
            JSON：result
        '''
        data = []

        sql = """
            SELECT
                t.id,
                t.mycustomer_id,
                t.transdate,
                c.compname,
                t.collectiondate,
                t.amountreceivables,
                t.amountpayin,
                t.amountend,
                u.username
            FROM
                transform t
                INNER JOIN customer c ON t.customer_id = c.id
                INNER JOIN user u ON u.id = t.receiver_id
            WHERE
                t.id in ({0})
        """.format(row_id)
        # print(1,sql)
        rows = TransForm.raw(sql)
        for row in rows:
            data.append({'id':row.id,
                         'mycustomer_id':row.mycustomer_id,
                         'transdate':str(row.transdate),
                         'compname':row.compname,
                         'collectiondate':str(row.collectiondate),
                         'amountreceivables':row.amountreceivables,
                         'amountpayin':row.amountpayin,
                         'amountend':row.amountend,
                         'username':row.username})

        result = {"data": data}
        return super().resultSet(200, "流转单详细查询成功", result)



